﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace StorageInterface
{
    public static class Data
    {
        public static List<Partner> GetAllPartners()
        {
            linqDataContext conn = new linqDataContext();
            return conn.Partners.ToList();
        }
        public static Partner NewPartner()
        {
            try
            {
                Partner p = new Partner();
                p.Name = "Контрагент";
                linqDataContext conn = new linqDataContext();
                conn.Partners.InsertOnSubmit(p);
                conn.SubmitChanges();
                
                return p;
            }
            catch (Exception e)
            {
                System.Windows.MessageBox.Show(e.Message);
                return null;
            }

        }
        public static void Submit()
        {
            linqDataContext conn = new linqDataContext();
            //conn.Partners.u
            conn.SubmitChanges();
        }
        public static void UpdatePartner(Partner p)
        {
            linqDataContext conn = new linqDataContext();
            var p1 = conn.Partners.Where(el => el.ID_Partner == p.ID_Partner).First();
            p1.Name = p.Name;
            p1.OKP =p.OKP;
            p1.Code =p.Code;
            p1.Email =p.Email;
            p1.FactAddress =p.FactAddress;
            p1.Faks =p.Faks;
            p1.KPP =p.KPP;
            p1.Discount = p.Discount;
            p1.LegalAddress =p.LegalAddress;
            p1.Name =p.Name;
            p1.OKP =p.OKP;
            p1.Telephone = p.Telephone;
            conn.SubmitChanges();
        }

        public static bool NewProduct(string name)
        {
            linqDataContext conn = new linqDataContext();
            int c = conn.Products.Count(el => el.Name == name);
            if (c == 0)
            {
                Product p = new Product();
                p.Name = name;
                conn.Products.InsertOnSubmit(p);
                conn.SubmitChanges();
                return true;
            }
            return false;
        }

        public static List<Product> GetAllProducts()
        {
            linqDataContext conn = new linqDataContext();
            return conn.Products.ToList();
        }

        public static void NewOrder(string namedoc, 
                               string comment,
                               double summ,
                               double amount, 
                               string state,
                               DateTime dt,
                               Partner partner, 
                               Product product)
        {
            linqDataContext conn = new linqDataContext();
            //Payment p = new Payment();
            //p.Date = dt;
            //p.Summ = summ;
            //conn.Payments.InsertOnSubmit(p);
            //conn.SubmitChanges();
            Document d = new Document();
            d.Name = namedoc;
            d.Comment = comment;
            conn.Documents.InsertOnSubmit(d);
            conn.SubmitChanges();

            Partner part_ner = conn.Partners.Where(el => el.ID_Partner == partner.ID_Partner).First();
            Product prod_uct = conn.Products.Where(el => el.ID_Product == product.ID_Product).First();

            Order order = new Order();
            order.Amount = amount;
            order.Document = d;
            order.Partner = part_ner;
            order.ID_Payment = null;
            order.Product = prod_uct;
            order.State = state;
            order.Date = dt;
            order.Summ = summ;
            


            conn.Orders.InsertOnSubmit(order);
            conn.SubmitChanges();
           
        }

        public static List<Order> GetOrders(DateTime? start,
                                                      DateTime? end,
                                                      Partner p)
        {
            linqDataContext conn = new linqDataContext();
            System.Data.DataTable table = new System.Data.DataTable();
            DateTime sdate = new DateTime(1900, 01, 01);
            if (start != null)
                sdate = start.Value;
            DateTime edate = new DateTime(2200, 01, 01);
            if (end != null)
                edate = end.Value;
            List<Order> orders = null;

            if (p != null)
                orders = p.Orders.Where(el => el.Date >= sdate && el.Date <= edate && p.ID_Partner == el.ID_partner).ToList();
            else
                orders = conn.Orders.Where(el => el.Date >= sdate && el.Date <= edate).ToList();
            //Order order = new Order();
            //order.Payment.Date
            return orders;
                 
        }
        public static void RemoveOrder(Order or)
        {
            linqDataContext conn = new linqDataContext();
            conn.Orders.DeleteOnSubmit(conn.Orders.Where(el=>el.ID_order == or.ID_order).First());
            conn.SubmitChanges();
        }

        public static List<Payment> GetPayments(bool isin, Partner p,Storage s, DateTime? start, DateTime? end)
        {
            linqDataContext conn = new linqDataContext();
            DateTime dt1 = start == null ? new DateTime(1800, 01, 01) : start.Value;
            DateTime dt2 = end == null ? new DateTime(2200, 01, 01) : end.Value;

            string q1 = @"SELECT *
                            FROM [StorageDB].[dbo].[Payment]  P,
	                        [StorageDB].[dbo].[Order]  O
	                        where O.ID_Payment = P.ID_Payment
	                        and O.ID_Partner = {0} 
	                        and P.Date >= {1}
	                        and P.Date <= {2}";
            string q2 = @"SELECT *
                            FROM [StorageDB].[dbo].[Payment]  P,
	                        [StorageDB].[dbo].[Order]  O
	                        where O.ID_Payment = P.ID_Payment
	                        and P.Date >= {0}
	                        and P.Date <= {1}";
            string q3 = @"SELECT *
                          FROM [StorageDB].[dbo].[Payment]  P,
	                           [StorageDB].[dbo].[WriteOnOff]  W
	                           where W.ID_Payment = P.ID_Payment
	                           and W.OnOrOff = 1
	                           and P.Date >= {0}
	                           and P.Date <= {1}  ";
	  
	   

            if (p == null)
            {
                if (isin)
                    return conn.ExecuteQuery<Payment>(q2, dt1, dt2).ToList();
                else
                    return conn.ExecuteQuery<Payment>(q3, dt1, dt2).ToList();
                
            }
            else
            {
                if (isin)
                    return conn.ExecuteQuery<Payment>(q1,p.ID_Partner,dt1, dt2).ToList();
                else
                    return conn.ExecuteQuery<Payment>(q3, dt1, dt2).ToList();
            }
            return null;
        }

        public static double GetAvaibleAmount(Product pr)
        {
            linqDataContext conn = new linqDataContext();
            var els = conn.ProductInStorages.Where(el => el.ID_Product == pr.ID_Product).Select(el => el.Amount);
            double c = 0;
            foreach (var el in els)
                c += el;
            return c;
        }
        public static void InsertPayment(Order ord, double summ,DateTime date)
        {
            if ( Data.GetAvaibleAmount(ord.Product) < ord.Amount)
                return;
            linqDataContext conn = new linqDataContext();
            var o = conn.Orders.Where(el => el.ID_order == ord.ID_order).First();
            o.State = "Выполнен";
            //1 - ищем все склады где есть этот продукт и сортируем их
            var liststorage = conn.ProductInStorages.Where(el => el.ID_Product == ord.Product.ID_Product).OrderBy(el => el.Amount).ToArray();
            Payment p = new Payment();
            p.Summ = summ;
            p.Date = date;
            o.Payment = p;
            conn.SubmitChanges();


            double am = 0;
            int i = 0;
            List<OrderFromStorage> list = new List<OrderFromStorage>();
            while (am != ord.Amount && i != liststorage.Count())
            {
                OrderFromStorage ofs = new OrderFromStorage();

                ofs.ID_order = ord.ID_order;
                ofs.ID_storage = liststorage[i].ID_Storage;
                double delta = ord.Amount - am;
                if (delta > liststorage[i].Amount)
                {
                    ofs.Amount = liststorage[i].Amount;
                    am += ofs.Amount;
                    liststorage[i].Amount = 0;
                }
                else
                {
                    ofs.Amount = delta;
                    am += delta;
                    liststorage[i].Amount -= delta;
                }
                ++i;
                list.Add(ofs);
            }
            conn.OrderFromStorages.InsertAllOnSubmit(list);
            conn.SubmitChanges();
        }
        public static void DeletePayment(Payment p)
        {
            linqDataContext conn = new linqDataContext();
            //Если Payment по ордеру
            #region order
            var listord = conn.Orders.Where(el => el.ID_Payment == p.ID_Payment).ToArray();
            if (listord.Count() != 0)
            {
                Order ord = listord.First();
                ord.ID_Payment = null;
                ord.State = "Выполняется";
                foreach (var el in ord.OrderFromStorages)
                {
                    var list = conn.ProductInStorages.Where(el1 => el1.ID_Product == ord.ID_product &&
                                                                             el1.ID_Storage == el.ID_storage);

                    if (list.Count() == 0)
                    {
                        ProductInStorage pis = new ProductInStorage();
                        pis.ID_Product = ord.ID_product;
                        pis.ID_Storage = el.ID_storage;
                        pis.Amount = ord.Amount;
                        conn.ProductInStorages.InsertOnSubmit(pis);
                        
                    }
                    else
                        list.First().Amount += ord.Amount;
                    conn.SubmitChanges();                    
                }
                conn.OrderFromStorages.DeleteAllOnSubmit(ord.OrderFromStorages.ToArray());
                conn.Payments.DeleteAllOnSubmit(conn.Payments.Where(el=>el.ID_Payment==p.ID_Payment));
                conn.SubmitChanges();
                return;
            }
            #endregion
            #region in
            var listwriteon = conn.WriteOnOffs.Where(el => el.ID_Payment == p.ID_Payment && el.OnOrOff == 1).ToArray();
            if (listwriteon.Count() != 0)
            {
                var wr = listwriteon.First();
                //проверяем не было ли выполнено заказов после поставки
                int count = conn.OrderFromStorages.Count(el => el.ID_storage == wr.ID_Storage && 
                    el.Order.ID_product == wr.ID_Product && p.Date <= el.Order.Payment.Date);
                if (count != 0)
                {
                    ErrorHandler.HandleError("Данный платеж не может быть удален, т.к. позднее были выполнены заказы, где была учтена поставка по данному платежу!");
                    return;
                }
                wr.Payment = null;
                var listproductinstorage = conn.ProductInStorages.Where(el1 => el1.ID_Storage == wr.ID_Storage && 
                                                        wr.ID_Product == el1.ID_Product);

                if (listproductinstorage.Count() == 0)
                {
                    ProductInStorage pis = new ProductInStorage();
                    pis.ID_Product = wr.ID_Product;
                    pis.ID_Storage = wr.ID_Storage;
                    pis.Amount = wr.Amount;
                    conn.ProductInStorages.InsertOnSubmit(pis);

                }
                else
                    listproductinstorage.First().Amount -= wr.Amount;

                conn.WriteOnOffs.DeleteOnSubmit(wr);
                conn.Payments.DeleteAllOnSubmit(conn.Payments.Where(el=>el.ID_Payment == p.ID_Payment));
                conn.SubmitChanges();
                
                return;
            }
            #endregion
        }
        public static void UpdateOrder(Order ord)
        {
            linqDataContext conn = new linqDataContext();
            var scon = conn.Orders.Where(el => el.ID_order == ord.ID_order).First();
            scon.State = ord.State;
            //scon.ID_Payment = ord.ID_Payment;
            conn.SubmitChanges();

        }
        public static List<Storage> GetAllStorages()
        {
            linqDataContext conn = new linqDataContext();
            return conn.Storages.ToList();
        }

        public static void NewOutputPayment(Product prdct, string namedoc,
                                            double amount, double summ,string comment,
                                            DateTime date, Storage storage)
        {
            try
            {
                linqDataContext conn = new linqDataContext();
                Document doc = new Document();
                doc.Comment = comment;
                doc.Name = namedoc;
                conn.Documents.InsertOnSubmit(doc);
                conn.SubmitChanges();

                Payment pay = new Payment();
                pay.Date = date;
                pay.Summ = summ;
                conn.Payments.InsertOnSubmit(pay);
                conn.SubmitChanges();

                WriteOnOff woof = new WriteOnOff();
                woof.Amount = amount;
                woof.Document = doc;
                woof.OnOrOff = 1;//On
                woof.ID_Product = prdct.ID_Product;
                woof.ID_Storage = storage.N_Storage;
                woof.Payment = pay;
                conn.WriteOnOffs.InsertOnSubmit(woof);
                conn.SubmitChanges();

                var list = conn.ProductInStorages.Where(el => el.ID_Product == prdct.ID_Product && el.ID_Storage == storage.N_Storage);
                ProductInStorage pis;
                if (list.Count() != 0)
                {
                    pis = list.First();
                    pis.Amount += amount;
                }
                else
                {
                    pis = new ProductInStorage();
                    
                    pis.Amount = amount;
                    pis.ID_Storage = storage.N_Storage;
                    pis.ID_Product = prdct.ID_Product;
                    conn.ProductInStorages.InsertOnSubmit(pis);
                }
                
                conn.SubmitChanges();
            }
            catch (Exception ex) 
            {
                ErrorHandler.HandleError(ex.Message);
            }
        }

        public static double GetAvaibleAmount(Storage st, Product pr)
        {
            if (st == null || pr == null)
                return 0;
            linqDataContext conn = new linqDataContext();
            var els = conn.ProductInStorages.Where(el => el.ID_Product == pr.ID_Product && el.ID_Storage == st.N_Storage).Select(el => el.Amount);
            double c = 0;
            foreach (var el in els)
                c += el;
            return c;

        }

        public static void NewMove(Storage st1, Storage st2, Product pr, double amount)
        {
            linqDataContext conn = new linqDataContext();
            if (st1 == null && st2 == null )
                return;

            if (st1 != null)
            {
               
                var lpis1 = conn.ProductInStorages.Where(el => el.ID_Storage == st1.N_Storage &&
                                                                           el.ID_Product == pr.ID_Product).First();
                
                lpis1.Amount -= amount;
                conn.SubmitChanges();
            }

            if (st2 != null)
            {
                var lpis2 = conn.ProductInStorages.Where(el => el.ID_Storage == st2.N_Storage &&
                                                                           el.ID_Product == pr.ID_Product).ToArray();
                ProductInStorage pis2 = null;
                if (lpis2.Count() == 0)
                {
                    pis2 = new ProductInStorage();
                    pis2.ID_Product = pr.ID_Product;
                    pis2.ID_Storage = st2.N_Storage;
                    pis2.Amount = amount;

                }
                else
                {
                    pis2 = lpis2.First();
                    pis2.Amount += amount;
                }
                conn.SubmitChanges();
            }

            Move move = new Move();
            move.Amount = amount;
            move.Date = DateTime.Now;
            move.Document = null;
            move.ID_product = pr.ID_Product;
            if (st1 != null)
                move.ID_from = st1.N_Storage;
            if (st2 != null)
                move.ID_to = st2.N_Storage;
            conn.Moves.InsertOnSubmit(move);
            conn.SubmitChanges(); 

        }
        public static List<Move> GetMove(DateTime? start, DateTime? end, Storage storage)
        {
            linqDataContext conn = new linqDataContext();
            System.Data.DataTable table = new System.Data.DataTable();
            DateTime sdate = new DateTime(1900, 01, 01);
            if (start != null)
                sdate = start.Value;
            DateTime edate = new DateTime(2200, 01, 01);
            if (end != null)
                edate = end.Value;
            if (storage == null)
                return conn.Moves.Where(el => el.Date >= sdate && el.Date <= edate && el.ID_from != null && el.ID_to != null).ToList();
            else
                return conn.Moves.Where(el => (el.ID_from == storage.N_Storage || el.ID_to == storage.N_Storage) && el.Date >= sdate && el.Date <= edate && el.ID_from != null && el.ID_to != null).ToList();

        }
        public static List<Move> GetInvent(DateTime? start, DateTime? end, Storage storage)
        {
            linqDataContext conn = new linqDataContext();
            DateTime sdate = new DateTime(1900, 01, 01);
            if (start != null)
                sdate = start.Value;
            DateTime edate = new DateTime(2200, 01, 01);
            if (end != null)
                edate = end.Value;
            if (storage == null)
                return conn.Moves.Where(el => el.Date >= sdate && el.Date <= edate && ((el.ID_from == null && el.ID_to != null) || (el.ID_from != null && el.ID_to == null))).ToList();
            else
                return conn.Moves.Where(el =>(el.ID_from == storage.N_Storage || el.ID_to == storage.N_Storage)
                       && el.Date >= sdate && el.Date <= edate && ((el.ID_from == null && el.ID_to != null) || (el.ID_from != null && el.ID_to == null))).ToList();
        }

        public static List<WriteOnOff> GetOprihod(DateTime? start, DateTime? end, Storage st)
        {
            linqDataContext conn = new linqDataContext();

            DateTime sdate = new DateTime(1900, 01, 01);
            if (start != null)
                sdate = start.Value;
            DateTime edate = new DateTime(2200, 01, 01);
            if (end != null)
                edate = end.Value;

            if (st == null)
                return conn.WriteOnOffs.Where(el => el.Payment.Date >= sdate && el.Payment.Date <= edate && el.OnOrOff == 1).ToList();
            else
                return conn.WriteOnOffs.Where(el =>el.ID_Storage == st.N_Storage && el.Payment.Date >= sdate && el.Payment.Date <= edate && el.OnOrOff == 1).ToList();
        }

        //возращает true Если для данного продукта выполнялиьс действия после указанной даты на данном складе
        private static bool IsDoAfterDate(DateTime dt, Product pr, Storage st)
        {
            linqDataContext conn = new linqDataContext();
            bool c1 = st.Moves.Where(el => el.ID_product == pr.ID_Product && el.Date >= dt).Count() == 0;
            if (!c1)
                return true;
            c1 = st.Moves1.Where(el => el.ID_product == pr.ID_Product && el.Date >= dt).Count() == 0;
            if (!c1)
                return true;
            c1 = st.WriteOnOffs.Where(el => el.Payment.Date >= dt && el.ID_Product == pr.ID_Product).Count() == 0;
            if (!c1)
                return true;
            c1 = st.OrderFromStorages.Where(el => el.Order.ID_product == pr.ID_Product && el.Order.Payment.Date >= dt && el.Order.State == "Выполнен").Count() == 0;
            if (!c1)
                return true;
            return false;
        }

        public static void DeleteMove(Move move)
        {
            linqDataContext conn = new linqDataContext();
            if (move.ID_from != null && IsDoAfterDate(move.Date, move.Product, move.Storage))
            {
                ErrorHandler.HandleError("Не возможно удалить элемент, т.к. эта операция учтена в последующих действия с данным продуктом и складом!!!");
                return;
            }
            if (move.ID_to != null && IsDoAfterDate(move.Date, move.Product, move.Storage1))
            {
                ErrorHandler.HandleError("Не возможно удалить элемент, т.к. эта операция учтена в последующих действия с данным продуктом и складом!!!");
                return;
            }
            ProductInStorage pis1 = null;
            ProductInStorage pis2 = null;
            try
            {
                pis1 = conn.ProductInStorages.Where(el => el.ID_Product == move.ID_product && el.ID_Storage == move.ID_from).First();
            }
            catch{}
            try
            {
                pis2 = conn.ProductInStorages.Where(el => el.ID_Product == move.ID_product && el.ID_Storage == move.ID_to).First();
            }
            catch { }
               
            if (move.ID_to != null && move.ID_from != null)
            {
                if (pis1 == null)
                {
                    pis1.ID_Storage = move.ID_from.Value;
                    pis1.ID_Product = move.ID_product;
                    pis1.Amount = move.Amount;
                    conn.ProductInStorages.InsertOnSubmit(pis1);
                }
                else
                    pis1.Amount += move.Amount;
                if (pis2 == null)
                {
                    pis2.ID_Storage = move.ID_from.Value;
                    pis2.ID_Product = move.ID_product;
                    pis2.Amount = 0;
                    conn.ProductInStorages.InsertOnSubmit(pis2);
                }
                else
                    pis2.Amount -= move.Amount;
                conn.Moves.DeleteAllOnSubmit(conn.Moves.Where(el => el.ID_Move == move.ID_Move));
                conn.SubmitChanges();
                return;
            }
            if (move.ID_to != null)
            {
                if (pis2 == null)
                {
                    pis2.ID_Storage = move.ID_from.Value;
                    pis2.ID_Product = move.ID_product;
                    pis2.Amount = 0;
                    conn.ProductInStorages.InsertOnSubmit(pis2);
                }
                else
                    pis2.Amount -= move.Amount;
                conn.Moves.DeleteAllOnSubmit(conn.Moves.Where(el => el.ID_Move == move.ID_Move));
                conn.SubmitChanges();
                return;
            }
            if (move.ID_from != null)
            {
                if (pis1 == null)
                {
                    pis1.ID_Storage = move.ID_from.Value;
                    pis1.ID_Product = move.ID_product;
                    pis1.Amount = move.Amount;
                    conn.ProductInStorages.InsertOnSubmit(pis1);
                }
                else
                    pis1.Amount += move.Amount;
                conn.Moves.DeleteAllOnSubmit(conn.Moves.Where(el => el.ID_Move == move.ID_Move));
                conn.SubmitChanges();
                return;
            }
        }

        public static void DeleteWriteOnOff(WriteOnOff wof)
        {
            linqDataContext conn = new linqDataContext();

        }

        public static List<ProductInStorage> GetPrInStrs(Storage s, Product p, bool? iszero)
        {
            linqDataContext conn = new linqDataContext();
            List<ProductInStorage> plist = conn.ProductInStorages.ToList();
            if (s != null)
                plist = plist.Where(el => el.ID_Storage == s.N_Storage).ToList();
            if (p != null)
                plist = plist.Where(el => el.ID_Product == p.ID_Product).ToList();
            if (iszero == null)
                return plist;
            else if (iszero.Value)
                return plist.Where(el => el.Amount == 0).ToList();
            else
                return plist.Where(el => el.Amount != 0).ToList();
            return plist;
            
        }
                                                         
    }
}
